from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os

# 数据库配置
DATABASE_URL = "sqlite:///./questions.db"

# 创建数据库引擎
engine = create_engine(
    DATABASE_URL, 
    connect_args={"check_same_thread": False},
    echo=True  # 开发时显示SQL语句
)

# 创建会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基类
Base = declarative_base()

# 数据库依赖
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Questions表模型
class Question(Base):
    __tablename__ = "questions"
    
    id = Column(Integer, primary_key=True, index=True)
    question_content = Column(String, nullable=False, index=True)  # 添加索引用于搜索
    option_a = Column(String, nullable=False)
    option_b = Column(String, nullable=False)
    option_c = Column(String, nullable=False)
    option_d = Column(String, nullable=False)
    answer = Column(String, nullable=False, index=True)  # 添加索引用于统计
    knowledge_point = Column(String, nullable=False, index=True)  # 添加索引用于分组查询
    explanation = Column(String, nullable=True)  # 详细答案解析
    wrong_analysis = Column(String, nullable=True)  # 错误选项分析
    created_time = Column(DateTime, default=datetime.now, index=True)  # 添加创建时间和索引
    updated_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # 添加更新时间
    difficulty_level = Column(String, nullable=True, index=True)  # 难度等级：easy/medium/hard
    topic_category = Column(String, nullable=True, index=True)  # 主题分类
    question_bank_id = Column(Integer, ForeignKey("question_banks.id", ondelete="CASCADE"), nullable=True, index=True)  # 所属题库ID
    
    # 关联关系
    submission_details = relationship("SubmissionDetail", back_populates="question", cascade="all, delete-orphan")
    question_bank = relationship("QuestionBank", back_populates="questions")

# Submissions表模型
class Submission(Base):
    __tablename__ = "submissions"
    
    id = Column(Integer, primary_key=True, index=True)
    student_name = Column(String, nullable=False, index=True)  # 添加索引用于学生查询
    score = Column(Integer, nullable=False, index=True)  # 添加索引用于成绩统计
    submission_date = Column(String, nullable=False, index=True)  # 添加索引用于日期查询
    class_id = Column(Integer, nullable=False, index=True)  # 添加索引用于班级查询
    submission_time = Column(DateTime, default=datetime.now, index=True)  # 添加索引用于时间排序
    client_ip = Column(String, nullable=True)  # 客户端IP地址
    total_questions = Column(Integer, nullable=False, default=0)  # 总题目数
    correct_answers = Column(Integer, nullable=False, default=0)  # 正确答案数
    accuracy_rate = Column(Integer, nullable=False, default=0)  # 正确率（百分比）
    
    # 关联到提交详情
    submission_details = relationship("SubmissionDetail", back_populates="submission", cascade="all, delete-orphan")

# SubmissionDetails表模型
class SubmissionDetail(Base):
    __tablename__ = "submission_details"
    
    id = Column(Integer, primary_key=True, index=True)
    submission_id = Column(Integer, ForeignKey("submissions.id", ondelete="CASCADE"), nullable=False, index=True)
    question_id = Column(Integer, ForeignKey("questions.id", ondelete="CASCADE"), nullable=False, index=True)
    selected_answer = Column(String, nullable=False, index=True)  # 学生选择的答案，添加索引用于统计
    is_correct = Column(Integer, nullable=False, default=0, index=True)  # 是否正确：0-错误，1-正确
    answer_time = Column(DateTime, default=datetime.now)  # 答题时间
    
    # 关联关系
    submission = relationship("Submission", back_populates="submission_details")
    question = relationship("Question", back_populates="submission_details")

# LLMConfig表模型 - 大语言模型配置
class LLMConfig(Base):
    __tablename__ = "llm_configs"
    
    id = Column(Integer, primary_key=True, index=True)
    config_name = Column(String, nullable=False, unique=True)  # 配置名称
    api_key = Column(String, nullable=False)  # API密钥
    base_url = Column(String, nullable=False)  # API基础URL
    model_name = Column(String, nullable=False)  # 模型名称
    is_active = Column(Integer, default=0)  # 是否为当前激活配置 (0/1)
    created_time = Column(DateTime, default=datetime.now)  # 创建时间
    updated_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # 更新时间

# QuestionBank表模型 - 题库管理
class QuestionBank(Base):
    __tablename__ = "question_banks"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, index=True)  # 题库名称
    description = Column(String, nullable=True)  # 题库描述
    created_time = Column(DateTime, default=datetime.now, index=True)  # 创建时间
    updated_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # 更新时间
    created_by = Column(String, nullable=True)  # 创建者
    is_active = Column(Integer, default=1)  # 是否激活：0-禁用，1-启用
    question_count = Column(Integer, default=0)  # 题目数量
    
    # 关联到题目
    questions = relationship("Question", back_populates="question_bank", cascade="all, delete-orphan")

# ClassConfig表模型 - 班级配置
class ClassConfig(Base):
    __tablename__ = "class_configs"
    
    id = Column(Integer, primary_key=True, index=True)
    current_class_id = Column(Integer, nullable=False, default=1)  # 当前班级ID
    class_name = Column(String, nullable=True)  # 班级名称
    updated_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # 更新时间

# 创建所有表
def create_tables():
    Base.metadata.create_all(bind=engine)

# 初始化数据库
if __name__ == "__main__":
    create_tables()
    print("数据库表创建完成！")